package org.fao.pims.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.ListIterator;
import org.fao.pims.model.InBook;;

public class BookDaoImpl extends BaseDao implements BookDao {

	public List getBookChapters() throws DataAccessException {
		
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        Connection conn = null;
        List result = new ArrayList();
       
        
      
        try {
            
        	conn = this.getDatasource().getConnection();        
            
            String query = "select  id, title, uri " + 
            				"from bib_document " + 
            				"where " + 
            				"ref_man_db_name = \"freebook\""; 
            				
            System.out.println(query);				
            
            pstmt = conn.prepareStatement(query);
            
            rset = pstmt.executeQuery();    
            
            result = makeBeanList(resultsetToList(rset));
            
            rset.close();
            
            pstmt.close();
            
            if( result.size() == 0 )
            	throw new DataAccessException();
                                               
            
        } 
        catch(SQLException e) {
            e.printStackTrace();
            
        }
        finally {
                        
            
            try 
            { 
                if( conn != null )
                    conn.close(); 
                System.out.println("Connection closed");
                
            }
            catch(Exception e) {
                
                e.printStackTrace();
               
            }
            
            
            
        }

		
		return result;
	}
	
	public List getBookChaptersByCrop(String scientificName) throws DataAccessException {
		
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        Connection conn = null;
        List chapterList = new ArrayList();
       
        
      
        try {
            
        	conn = this.getDatasource().getConnection();        
            
        	String query = "SELECT d.id, d.title, d.uri " + 
					    	"FROM bib_document as d, bib_subject as s, bib_document_subject as r " + 
					    	"where " + 
					    	"d.ref_man_db_name = \"freebook\" and " + 
					    	"s.term = \"" + scientificName.trim() + "\" and " +
					    	"s.id = r.subject and " +
					    	"d.id = r.document";
            				
        	System.out.println(query);
        	
            pstmt = conn.prepareStatement(query);
            
            rset = pstmt.executeQuery();    
            
            chapterList = makeBeanList(resultsetToList(rset));
            
            rset.close();
            
            pstmt.close();
            
            if( chapterList.size() == 0 )
            	throw new DataAccessException();
                                               
            
        } 
        catch(SQLException e) {
            e.printStackTrace();
            
        }
        finally {
                        
            
            try 
            { 
                if( conn != null )
                    conn.close(); 
                System.out.println("Connection closed");
                
            }
            catch(Exception e) {
                
                e.printStackTrace();
               
            }
            
            
            
        }

		
		
		return chapterList;
	}
	
	/**
     * Preleva i dati del result set contenuti in un oggetto List e li salva in un 
     * bean appropriato, facendo corrispondere un bean a ciascun record.
     * Restituisce una lista di bean
     */
    protected List makeBeanList(List rows)
    {
        List beanList = new ArrayList();
         
        ListIterator rowIterator = rows.listIterator();
        int rowcounter = 0;
        while(rowIterator.hasNext())
        {
            rowcounter++;
            List fieldlist = (List)rowIterator.next();
            ListIterator fieldsIterator = fieldlist.listIterator();
            Object obj = null;
            String type = "";
            String columnName = "";
            InBook bean = new InBook();
            
            
            while(fieldsIterator.hasNext())
            {
                TableFieldHandler tfhandler = (TableFieldHandler)fieldsIterator.next();
                obj = tfhandler.getObject();
                type = tfhandler.getType();
                columnName = tfhandler.getColumnName().trim();
                
                if( columnName.equalsIgnoreCase("ID") )
                {
                    bean.setId( ((Integer)obj).intValue() );
                    
                }                
                if( columnName.equalsIgnoreCase("TITLE") )
                {
                    bean.setTitle( (String)obj );
                    
                }
                if( columnName.equalsIgnoreCase("URI") )
                {
                    bean.setUrl((String)obj );
                    
                }
                
                               
                         
            }
            
            beanList.add(bean);
            
         }
        
        return beanList;
        
    }


}
